/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Controllers;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;
import Models.Complaint;

/**
 *
 * @author Bi
 */
public class ComplaintController {

    static Connection conn = DBUtility.openConnection();

    public static List<Complaint> LoadAllComplaint() {
        List<Complaint> list = new ArrayList<Complaint>();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareCall("select * from Complaints");
            rs = ps.executeQuery();
            while (rs.next()) {
                Complaint complaint = new Complaint();
                complaint.setID(rs.getInt("ComplaintID"));
                complaint.setName(rs.getString("ComplainerName"));
                complaint.setAddress(rs.getString("ComplainerAddress"));
                complaint.setPhone(rs.getString("ComplainerPhone"));
                complaint.setEmail(rs.getString("ComplainerEmail"));
                complaint.setType(rs.getString("OffenceType"));
                complaint.setOffTime(rs.getDate("OffenceTime"));
                complaint.setPlace(rs.getString("OffencePlace"));
                complaint.setOfficerID(rs.getInt("OfficerID"));
                complaint.setInvovelment(rs.getString("Involvements"));
                complaint.setDescription(rs.getString("Description"));
                complaint.setComplaintStatus(rs.getInt("ComplaintStatus"));
                list.add(complaint);
            }

        } catch (SQLException ex) {
        } finally {

            DBUtility.closeResultSet(rs);
            DBUtility.closeStatement(ps);
            DBUtility.closeConnection(conn);
        }

        return list;
    }

    public static boolean AddComplaint(Complaint newComplaint) {
        String userId = System.getProperty("userId");
        CallableStatement cs;
        PreparedStatement ps;
        ResultSet rs;
        try {
            cs = conn.prepareCall("{call sp_AddComplaint(?,?,?,?,?,?,?,?,?,?,?)}");
            cs.setString("ComplainerName", newComplaint.getName());
            cs.setString("ComplainerAddress", newComplaint.getAddress());
            cs.setString("ComplainerPhone", newComplaint.getPhone());
            cs.setString("ComplainerEmail", newComplaint.getEmail());
            Date _sqlDate = new Date(newComplaint.getOffTime().getTime());
            cs.setDate("OffenceTime", _sqlDate);
            cs.setString("OffenceType", newComplaint.getType());
            cs.setString("OffencePlace", newComplaint.getPlace());
            cs.setInt("OfficerID", newComplaint.getOfficerID());
            cs.setString("Involvements", newComplaint.getInvovelment());
            cs.setString("Description", newComplaint.getDescription());
            cs.setInt("ComplaintStatus", newComplaint.getComplaintStatus());
            if (cs.executeUpdate() > 0) {
                JOptionPane.showMessageDialog(null, "New complaint submitted.");
                return true;
            } else {
                JOptionPane.showMessageDialog(null, "Submission failed.");
                return false;
            }

        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        return true;
    }

    public static Complaint GetComplaintByID(int ID) {
        try {
            CallableStatement cs = conn.prepareCall("{call sp_GetComplaintByID(?)}");
            cs.setInt(1, ID);
            ResultSet rs = cs.executeQuery();
            if (rs.next()) {
                Complaint _complaint = new Complaint();
                _complaint.setID(rs.getInt("ComplaintID"));
                _complaint.setName(rs.getString("ComplainerName"));
                _complaint.setAddress(rs.getString("ComplainerAddress"));
                _complaint.setPhone(rs.getString("ComplainerPhone"));
                _complaint.setEmail(rs.getString("ComplainerEmail"));
                _complaint.setType(rs.getString("OffenceType"));
                _complaint.setOffTime(rs.getDate("OffenceTime"));
                _complaint.setPlace(rs.getString("OffencePlace"));
                _complaint.setInvovelment(rs.getString("Involvements"));
                _complaint.setDescription(rs.getString("Description"));
                _complaint.setComplaintStatus(rs.getInt("ComplaintStatus"));

                return _complaint;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public static boolean EditComplaint(Complaint newComplaint) {
        CallableStatement cs;
//        PreparedStatement ps;
        ResultSet rs;
        try {
            cs = conn.prepareCall("{call sp_EditComplaint(?,?,?,?,?,?,?,?,?,?,?)}");
            cs.setString("ComplainerName", newComplaint.getName());
            cs.setString("ComplainerAddress", newComplaint.getAddress());
            cs.setString("ComplainerPhone", newComplaint.getPhone());
            cs.setString("ComplainerEmail", newComplaint.getEmail());
            Date _sqlDate = new Date(newComplaint.getOffTime().getTime());
            cs.setDate("OffenceTime", _sqlDate);
            cs.setString("OffenceType", newComplaint.getType());
            cs.setString("OffencePlace", newComplaint.getPlace());
            cs.setInt("OfficerID", newComplaint.getOfficerID());
            cs.setString("Involvements", newComplaint.getInvovelment());
            cs.setString("Description", newComplaint.getDescription());
            cs.setInt("ComplaintStatus", newComplaint.getComplaintStatus());
            if (cs.executeUpdate() > 0) {
                JOptionPane.showMessageDialog(null, "New Complaint submitted.");
                return true;
            } else {
                JOptionPane.showMessageDialog(null, "Submission failed.");
                return false;
            }

        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        return true;
    }
}
